package cn.sytton.taffe.excel;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.sytton.taffe.excel.adapter.ExcelImportDataAdapter;
import cn.sytton.taffe.excel.annotation.ImportExcel;
import cn.sytton.taffe.excel.model.ExcelImportResult;
import cn.sytton.taffe.file.entity.FileEntity;
import cn.sytton.taffe.file.service.impl.FileTempService;
import cn.sytton.taffe.framework.exception.BizException;
import cn.sytton.taffe.framework.util.Convert;
import cn.sytton.taffe.framework.util.DateUtil;
import cn.sytton.taffe.framework.util.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel导入工具类
 *
 * @author skyrock
 */
@Slf4j
public class ExcelImport<T> {

    private Class<T> clazz;

    /**
     * 导入文件
     */
    private MultipartFile file;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 当前导入的sheet;
     */
    private Sheet sheet;

    /**
     * 最后已列
     */
    private int lastCol;
    /**
     * 导入结果
     */
    private final LinkedList<ExcelImportResult<T>> result = new LinkedList<>();

    public LinkedList<ExcelImportResult<T>> getResult() {
        return result;
    }

    public ExcelImport(Class<T> clazz, MultipartFile file) {
        this.file = file;
        this.clazz = clazz;
    }

    public void importExcel() {
        try {
            wb = WorkbookFactory.create(file.getInputStream());
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new BizException("导入错误");
        }
        sheet = wb.getSheetAt(0);

        // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
        int rows = sheet.getLastRowNum();

        if (rows > 0) {
            // 定义一个map用于存放excel列的序号和field.
            Map<String, Integer> cellMap = new HashMap<String, Integer>();
            // 获取表头
            Row heard = sheet.getRow(0);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
                Cell cell = heard.getCell(i);
                if (cell != null) {
                    String fileName = this.getCellValue(heard, i).toString();
                    cellMap.put(fileName, i);
                } else {
                    cellMap.put(null, i);
                }
                lastCol = i;
            }
            // 有数据时才处理 得到类的所有field.
            List<Object[]> fields = this.getFields();
            Map<Integer, Object[]> fieldsMap = new HashMap<>();
            for (Object[] objects : fields) {
                ImportExcel attr = (ImportExcel) objects[1];
                Integer column = cellMap.get(attr.value());
                if (column != null) {
                    fieldsMap.put(column, objects);
                }
            }
            for (int i = 1; i <= rows; i++) {
                // 从第2行开始取数据,默认第一行是表头.
                Row row = sheet.getRow(i);
                // 判断当前行是否是空行
                if (isRowEmpty(row)) {
                    continue;
                }

                ExcelImportResult<T> entityResult = new ExcelImportResult<>();
                try {
                    entityResult.setEntity(getRowData(row, fieldsMap));
                } catch (Exception e) {
                    entityResult.setSuccess(false);
                    entityResult.setError(e.getMessage());
                }
                result.add(entityResult);
            }
        }
    }

    /**
     * 获取字段注解信息
     */
    private List<Object[]> getFields() {
        List<Object[]> fields = new ArrayList<Object[]>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields) {
            // 单注解
            if (field.isAnnotationPresent(ImportExcel.class)) {
                ImportExcel attr = field.getAnnotation(ImportExcel.class);
                field.setAccessible(true);
                fields.add(new Object[]{field, attr});
            }
        }
        return fields;
    }

    private T getRowData(Row row, Map<Integer, Object[]> fieldsMap) throws Exception {
        T entity = null;
        for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {
            Object val = this.getCellValue(row, entry.getKey());
            // 如果不存在实例则新建.
            entity = (entity == null ? clazz.newInstance() : entity);
            // 从map中得到对应列的field.
            Field field = (Field) entry.getValue()[0];
            ImportExcel attr = (ImportExcel) entry.getValue()[1];
            try {
                // 取得类型,并根据对象类型设置值.
                Class<?> fieldType = field.getType();
                if (String.class == fieldType) {
                    String s = Convert.toStr(val);
                } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(val.toString())) {
                    val = Convert.toInt(val);
                } else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
                    val = Convert.toLong(val);
                } else if (Double.TYPE == fieldType || Double.class == fieldType) {
                    val = Convert.toDouble(val);
                } else if (Float.TYPE == fieldType || Float.class == fieldType) {
                    val = Convert.toFloat(val);
                } else if (BigDecimal.class == fieldType) {
                    val = Convert.toBigDecimal(val);
                } else if (Date.class == fieldType) {
                    if (val instanceof String) {
                        val = DateUtil.parseDate(val.toString());
                    } else if (val instanceof Double) {
                        val = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) val);
                    }
                } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
                    val = Convert.toBool(val, false);
                }

                String propertyName = field.getName();
                if (StringUtils.isNotEmpty(attr.converterExp())) {
                    val = reverseByExp(Convert.toStr(val), attr.converterExp(), ",");
                } else if (StringUtils.isNotEmpty(attr.dataAdapterBeanId())) {
                    val = dataAdapterHandler(val, attr.dataAdapterBeanId());
                }
                ReflectUtil.setFieldValue(entity, propertyName, val);
            } catch (Exception e) {
                String msg = "["+attr.value()+"]数据或格式错误," + e.getMessage();
                log.error(msg, e);
                throw new BizException(msg);
            }
        }
        return entity;
    }

    /**
     * 判断是否是空行
     *
     * @param row 判断的行
     * @return
     */
    private boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    private Object getCellValue(Row row, int column) {
        if (row == null) {
            return null;
        }
        Object val = "";
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
                val = cell.getNumericCellValue();
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    val = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                } else {
                    if ((Double) val % 1 != 0) {
                        val = new BigDecimal(val.toString());
                    } else {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            } else if (cell.getCellType() == CellType.STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == CellType.BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == CellType.ERROR) {
                val = cell.getErrorCellValue();
            }
        }
        return val;
    }

    /**
     * 反向解析值 男=0,女=1,未知=2
     *
     * @param propertyValue 参数值
     * @param converterExp  翻译注解
     * @param separator     分隔符
     * @return 解析后值
     */
    private static String reverseByExp(String propertyValue, String converterExp, String separator) {
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource = converterExp.split(",");
        for (String item : convertSource) {
            String[] itemArray = item.split("=");
            if (StringUtils.containsAny(propertyValue, separator)) {
                for (String value : propertyValue.split(separator)) {
                    if (itemArray[1].equals(value)) {
                        propertyString.append(itemArray[0]).append(separator);
                        break;
                    }
                }
            } else {
                if (itemArray[1].equals(propertyValue)) {
                    return itemArray[0];
                }
            }
        }
        return StringUtils.stripEnd(propertyString.toString(), separator);
    }


    /**
     * 数据处理器
     *
     * @param value 数据值
     * @return 返回值
     */
    private String dataAdapterHandler(Object value, String beanId) {
        if (SpringContextUtil.containsBean(beanId)) {
            ExcelImportDataAdapter da = SpringContextUtil.getBean(beanId);
            return Convert.toStr(da.convert(value));
        } else {
            log.warn("实现类:" + beanId + " 不存在");
            return Convert.toStr(value);
        }
    }

    /**
     * 记录导入最终结果, 记录到文件内并且存储
     * @return 返回文件名
     */
    public String writeImportResult() {
        try {
            for (int i = 0; i < result.size(); i++) {
                ExcelImportResult<T> er = result.get(i);
                Row row = sheet.getRow(i + 1);
                Cell cell = row.createCell(lastCol + 1);
                if (er.isSuccess()) {
                    cell.setCellValue("成功");
                } else {
                    cell.setCellValue("失败: " + er.getError());
                    CellStyle style = wb.createCellStyle();
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    style.setBorderRight(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderTop(BorderStyle.THIN);
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(IndexedColors.ROSE.getIndex());
                    for(int c = 0; c <= lastCol + 1; c++) {
                        row.getCell(c).setCellStyle(style);
                    }
                }
            }

            FileTempService fileTempService = SpringContextUtil.getBean("fileTempService");
            String resultFileName = FileUtil.getPrefix(file.getOriginalFilename()) + "_导入结果"+"."+FileUtil.getSuffix(file.getOriginalFilename());
            File resultFile = new File(FileUtil.getTmpDir(), resultFileName);
            OutputStream os = new FileOutputStream(resultFile);
            wb.write(os);
            os.flush();
            os.close();

            FileEntity fileEntity = fileTempService.upload(resultFile);

            FileUtil.del(resultFile);

            return fileEntity.getObjectName();
        } catch (Exception e) {
            log.error("导入结果写入失败:" + e.getMessage(), e);
            return "";
        }
    }
}
